package twitter.tool;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.List;

import twitter.entity.SimilarAccount;
import twitter.entity.Stat;
import twitter.entity.Tweet;

public class Database {

	private Connection conn;

	public Database(Connection connection) {
		conn = connection;
	}

	public void createTableTweet() throws SQLException {

		Statement stmt = conn.createStatement();
		stmt.executeUpdate("CREATE TABLE tweet"
				+ "(id VARCHAR(100) primary key, " + "date DATE, "
				+ "text TEXT, " + "retweets INTEGER, " + "favorites INTEGER, "
				+ "hashtags TEXT, " + "sentiment TEXT, " + "content TEXT)");

		stmt.close();

	}

	public void createTableStat() throws SQLException {

		Statement stmt = conn.createStatement();
		stmt.executeUpdate("CREATE TABLE stat" + "(date DATE primary key, "
				+ "follower INTEGER, " + "following INTEGER, " + "ar FLOAT, "
				+ "af FLOAT, nt INTEGER)");

		stmt.close();

	}

	private String allHashtags(Tweet tweet) {
		String hashtags = null;
		if (tweet.getHashtags().isEmpty())
			hashtags = "no hashtag";
		else {
			hashtags = tweet.getHashtags().get(0);
			for (int i = 1; i < tweet.getHashtags().size(); i++) {
				hashtags = hashtags.concat("," + tweet.getHashtags().get(i));
			}
		}
		return hashtags;
	}

	public void initTweet(List<Tweet> tweets) throws SQLException {
		for (Tweet tweet : tweets) {

			PreparedStatement pstmt = conn
					.prepareStatement("INSERT INTO tweet"
							+ "(id,date,text,retweets,favorites,hashtags,sentiment,content) VALUES"
							+ "(?,?,?,?,?,?,?,?)");

			pstmt.setString(1, tweet.getId());
			pstmt.setDate(2, (Date) tweet.getDate());
			pstmt.setString(3, tweet.getText());
			pstmt.setInt(4, tweet.getRetweet_count());
			pstmt.setInt(5, tweet.getFavorite_count());
			pstmt.setString(6, allHashtags(tweet));
			pstmt.setString(7, tweet.getSentiment());
			pstmt.setString(8, tweet.getContent());

			pstmt.executeUpdate();

			pstmt.close();
		}

	}

	public void updateTweet(List<Tweet> tweets) throws SQLException {
		deleteAllTweet();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT id FROM tweet");
		boolean n = true;
		for (Tweet tweet : tweets) {
			while (rs.next() && n) {
				if (tweet.getId().equals(rs.getString("id"))) {
					n = false;
				}
			}
			if (n) {
				PreparedStatement pstmt = conn
						.prepareStatement("INSERT INTO tweet"
								+ "(id,date,text,retweets,favorites,hashtags,sentiment,content) VALUES"
								+ "(?,?,?,?,?,?,?,?)");

				pstmt.setString(1, tweet.getId());
				pstmt.setDate(2, (Date) tweet.getDate());
				pstmt.setString(3, tweet.getText());
				pstmt.setInt(4, tweet.getRetweet_count());
				pstmt.setInt(5, tweet.getFavorite_count());
				pstmt.setString(6, allHashtags(tweet));
				pstmt.setString(7, tweet.getSentiment());
				pstmt.setString(8, tweet.getContent());

				pstmt.executeUpdate();

				pstmt.close();
			}
			n = true;
			rs.beforeFirst();

		}
	}

	public List<Tweet> searchTag(List<Tweet> tweets, String search)
			throws SQLException {
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT * FROM tweet WHERE hashtags LIKE '%"
						+ search + "%'");
		Parser p = new Parser();
		List<Tweet> t = p.parseTweets(rs);
		stmt.close();
		return t;

	}

	public List<Tweet> searchContent(List<Tweet> tweets, String search)
			throws SQLException {
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT * FROM tweet WHERE content LIKE '%"
						+ search + "%'");
		Parser p = new Parser();
		List<Tweet> t = p.parseTweets(rs);
		stmt.close();
		return t;

	}

	public List<Tweet> getAllTweet() throws SQLException {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM tweet");
		Parser p = new Parser();
		List<Tweet> tweets = p.parseTweets(rs);
		s.close();
		return tweets;
	}

	public void deleteAllTweet() throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM tweet");
		stmt.close();
	}

	public List<Stat> getAllStat() throws SQLException {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM stat");
		Parser p = new Parser();
		List<Stat> stats = p.parseStats(rs);
		s.close();
		return stats;

	}

	public void insertStat(Stat s) throws SQLException {
		List<Stat> stats = getAllStat();
		boolean found = false;
		for (int i = 0; i < stats.size() && !found; i++) {
			Calendar c1 = Calendar.getInstance();
			c1.setTime(s.getDate());
			Calendar c2 = Calendar.getInstance();
			c2.setTime(stats.get(i).getDate());
			if (c2.get(Calendar.YEAR) == c1.get(Calendar.YEAR)
					&& c2.get(Calendar.DAY_OF_YEAR) == c1
							.get(Calendar.DAY_OF_YEAR)) {
				found = true;
			}
		}
		if (!found) {
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO stat"
					+ "(date,follower,following,ar,af,nt) VALUES"
					+ "(?,?,?,?,?,?)");

			pstmt.setDate(1, s.getDate());
			pstmt.setInt(2, s.getFollower());
			pstmt.setInt(3, s.getFollowing());
			pstmt.setFloat(4, s.getAr());
			pstmt.setFloat(5, s.getAf());
			pstmt.setInt(6, s.getNt());

			pstmt.executeUpdate();

			pstmt.close();
		}
	}

	public void createTableSimilarAccount() throws SQLException {

		Statement stmt = conn.createStatement();
		stmt.executeUpdate("CREATE TABLE similaraccount"
				+ "(id VARCHAR(100) primary key, " + "twittername TEXT, "
				+ "facebookname TEXT)");

		stmt.close();
	}

	public void insertSimilarAccount(List<SimilarAccount> similarAccounts)
			throws SQLException {

		Long lastId = Long.valueOf(getLastId());
		for (SimilarAccount account : similarAccounts) {
			lastId++;
			PreparedStatement pstmt = conn
					.prepareStatement("INSERT INTO similaraccount"
							+ "(id,twittername,facebookname) VALUES"
							+ "(?,?,?)");
			pstmt.setString(1, String.valueOf(lastId));
			pstmt.setString(2, account.getTwitterName());
			pstmt.setString(3, account.getFacebookName());
			pstmt.executeUpdate();
			pstmt.close();

		}
	}

	private String getLastId() throws SQLException {
		Statement s = conn.createStatement();
		ResultSet rs;
		String lastId;
		try {
			rs = s.executeQuery("SELECT id FROM similaraccount ORDER BY id DESC LIMIT 1");
			rs.first();
			lastId = rs.getString("id");

		} catch (SQLException e) {
			e.printStackTrace();
			lastId = "-1";
		}
		return lastId;
	}

	public List<SimilarAccount> getAllSimilarAccounts() throws SQLException {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM similaraccount");
		Parser p = new Parser();
		List<SimilarAccount> similarAccounts = p.parseSimlarAccount(rs);
		s.close();
		return similarAccounts;
	}

	public void deleteAllSimilarAccounts() throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM similaraccount");
		stmt.close();
	}

	public void deleteSimilarAccount(String id) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM similaraccount WHERE id LIKE '" + id
				+ "'");
		stmt.close();
	}

	public SimilarAccount getSimilarAccountById(String id) throws SQLException {
		Statement s = conn.createStatement();
		ResultSet rs = s
				.executeQuery("SELECT * FROM similaraccount WHERE id LIKE '"
						+ id + "' LIMIT 1");
		Parser p = new Parser();
		List<SimilarAccount> similarAccounts = p.parseSimlarAccount(rs);
		s.close();
		return similarAccounts.get(0);
	}
}
